﻿using MySql.Data.MySqlClient;
using NB.MGJ.FrameWork;
using NB.MGJ.Model;
using Senparc.Weixin.MP.AdvancedAPIs.OAuth;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NB.MGJ.Service
{
    /// <summary>
    /// 促销活动 手机端
    /// </summary>
    public partial class MarketService
    {
       

        #region 补全 用户电话和真实姓名
        /// <summary>
        ///  补全 用户电话和真实姓名
        /// </summary>
        /// <param name="TName">表名</param>
        /// <param name="Uid">用户ID</param>
        /// <param name="phone">电话号码</param>
        /// <param name="name">用户真实姓名</param>
        /// <returns></returns>
        public void UpdateUser(string TName, int Uid, string phone)
        {
            string sql = string.Format(" update {0} set Phone=@Phone  where ID=@ID ", "marketing." + TName);
            MySqlParameter[] parameters =
            {
                    new MySqlParameter("@Phone", phone),
                  new MySqlParameter("@ID", Uid),
            };
            _baseRepository.ExecuteSql<int>(sql, parameters);

        }
        #endregion

        #region 添加用户
        /// <summary>
        /// 添加用户方法（通过传递实体类添加数据，把添加的数据赋值给实体类属性，不添加的值不填写）
        /// </summary>
        /// <param name="UserModel">User0001Model实体类</param>
        /// <returns>int 0为失败 ；1为成功</returns>
        public int InsertUser(OAuthUserInfo model, string Ecode)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into marketing.Marketing_User" + Ecode + " (");
            strSql.Append("OpenID,nickname,Sex,Country,Provnice,City,Language,HeadImgUrl,OA_Time,UnionId,GroupID,EnterpriseCode,Phone,Name ) ");
            strSql.Append(" values (");
            strSql.Append("@OpenID,@nickname,@Sex,@Country,@Provnice,@City,@Language,@HeadImgUrl,@OA_Time,@UnionId,@GroupID,@EnterpriseCode,@Phone,@Name) ");
            MySqlParameter[] parameters = {
                    new MySqlParameter("@OpenID", model.openid),
                    new MySqlParameter("@nickname",model.nickname),
                    new MySqlParameter("@Sex",model.sex),
                    new MySqlParameter("@Country", model.country),
                    new MySqlParameter("@Provnice", model.province),
                    new MySqlParameter("@City", model.city),
                    new MySqlParameter("@Language", ""),
                    new MySqlParameter("@HeadImgUrl",model.headimgurl ),
                    new MySqlParameter("@OA_Time",  DateTime.Now ),
                    new MySqlParameter("@UnionId", model.unionid),
                    new MySqlParameter("@GroupID",0),
                    new MySqlParameter("@EnterpriseCode", Ecode),
                    new MySqlParameter("@Phone", ""),
                    new MySqlParameter("@Name", "")};

            return _baseRepository.ExecuteText<int>(strSql.ToString(), parameters);

        }

        #endregion


        #region 客户信息
        //页面数据
        public PagedList<Marketing_User> GetMarketingUserService(ref PagerModel page, string msearch, string code)
        {
            if (_dhb.TabExists("marketing", "marketing_thewinning" + code))
            {
                page.FldName = string.Format(@"ID,Name,GroupID,OpenID,HeadImgUrl,Nickname, Provnice, Phone, City, Country,
                            (
                            	SELECT
                            		count(*)
                            	FROM
                            		marketing.marketing_thewinning{0} t
                            	WHERE
                            		u.ID = t.UserID
                            
                            ) as count, OA_Time ", code);
            }
            else
            {
                page.FldName = string.Format(@"ID,Name,GroupID,OpenID,HeadImgUrl,Nickname, Provnice, Phone, City, Country,
                          0 as count, OA_Time ", code);
            }
            page.TblName = @" marketing.Marketing_User" + code +" as u";
            string StrCondition = " 1=1 ";
            if (!string.IsNullOrWhiteSpace(page.StrCondition))
            {
                StrCondition = page.StrCondition;
            }

            page.StrCondition = StrCondition;
            if (string.IsNullOrEmpty(page.FldSort) || page.FldSort == "")
                page.FldSort = " OA_Time  desc ";

            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref page);
            IEnumerable<Marketing_User> resultList = _baseRepository.ExecuteStoredProcedureCollection<Marketing_User>("Common_PageList", parameters);
            return new PagedList<Marketing_User>(resultList, page.Page, page.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }
        /// <summary>
        /// 指定发送奖品 添加获奖明细
        /// </summary>
        /// <param name="model"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public int AddWinning(Marketing_TheWinning model, string code)
        {
            string sql = "insert into marketing.Marketing_TheWinning{9}(`ActivityID`,`UserID`,`AwardsID`,`Time`,`Groupid`,`State`,`Denomination`,`Remark`,`Code`) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}');SELECT @@IDENTITY;";
            sql = string.Format(sql, model.ActivityID, model.UserID, model.AwardsID, model.Time, model.Groupid, model.State, model.Denomination, code,model.Remark, model.Code);
            return _baseRepository.ExecuteSql<int>(sql);
        }
        public int AddWinningOfData(Marketing_TheWinning model, string code)
        {
            string sql = "insert into marketing.Marketing_TheWinning{13}(`ActivityID`,`UserID`,`AwardsID`,`Time`,`Groupid`,`BatchID`,`State`,`Denomination`,`Data`,`DataType`,`DataId`,`Remark`,`Code`) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}');SELECT @@IDENTITY;";
            sql = string.Format(sql, model.ActivityID, model.UserID, model.AwardsID, model.Time, model.Groupid, model.BatchID, model.State, model.Denomination, model.Data, model.DataType, model.DataId, model.Remark, model.Code, code);

            return _baseRepository.ExecuteSql<int>(sql);
        }

        //导出数据
        public IEnumerable<Marketing_User> GetExecls(string msearch, string code)
        {
            var rs = "SELECT * FROM marketing.Marketing_User" + code;
            if (!string.IsNullOrWhiteSpace(msearch))
            {
                rs += " where Nickname like '%" + msearch + "%'";
            }
            return _baseRepository.ExecuteSqlCollection<Marketing_User>(rs);
        }
        #endregion

        #region    活动模板数据操作
        /// <summary>
        /// 新增活动模板
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int TemplateSave(Marketing_Template model)
        {
            return _baseRepository.InsertReturnID<Marketing_Template>(model);
        }
        public int UpdateTemplate(Marketing_Template model)
        {
            return _baseRepository.Update<Marketing_Template>(model);
        }
        public Marketing_Template GetTemplateModel(int id)
        {
            string sql = "SELECT * FROM `marketing_template` where id=" + id;
            return _baseRepository.ExecuteText<Marketing_Template>(sql);
        }
        public Marketing_Template GetTemplateByAid(int aId)
        {
            string sql = "select * from marketing_template where id=(select TemplateID from marketing_activity where id=" + aId + ")";
            return _baseRepository.ExecuteText<Marketing_Template>(sql);
        }
        public Marketing_Activity GetActivityById(int id)
        {

            string sql = "select * from Marketing_Activity where id=" + id + " and ViewDel!=1";
            return _baseRepository.ExecuteText<Marketing_Activity>(sql);
        }

        /// <summary>
        ///     活动派送方式
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Marketing_Delivery GetDeliveryById(int id)
        {
            string sql = "select * from Marketing_Delivery where id=" + id;
            return _baseRepository.ExecuteText<Marketing_Delivery>(sql);
        }
        #endregion

        #region 根据活动编号 中奖项
        /// <summary>
        /// 根据活动编号 查询奖项 根据活动ID
        /// </summary>
        /// <param name="id">活动编号</param>
        /// <returns></returns>
        public List<Marketing_Awards> GetAwards_Aid(int id)
        {
            string sql = string.Format("select a.*,e.EnterpriseName,j.Denomination,j.type from Marketing_Awards a left join Organization_Enterprise e on a.EnterpriseId=e.ID  left join Marketing_Jackpot j on j.ID=a.JackpotID where activityID={0} order by a.Sequence  ", id);
            List<Marketing_Awards> list = _baseRepository.ExecuteSqlCollection<Marketing_Awards>(sql).ToList();
            return list;
        }
        #endregion

        #region 判断该扫码的数据是否存在
        /// <summary>
        /// 判断中奖纪录是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="code"></param>
        /// <param name="uTableName"></param>
        /// <returns></returns>
        public Marketing_TheWinning IsTheWinningData(string tableName, string code, string uTableName)
        {
            string sql = " select count(*) from " + tableName + " where code='" + code + "'";
            NB.MGJ.Common.WeiXinHelper.WriteLog("IsTheWinningDataSQL1", sql);
            int num = Convert.ToInt32(_baseRepository.GetSingle(sql));
            if (num == 1)
            {
                string sql2 = "  select *,u.Nickname from  " + tableName + " t left JOIN " + uTableName + "  u on t.UserID=u.ID where code='" + code + "'";
                NB.MGJ.Common.WeiXinHelper.WriteLog("IsTheWinningDataSQL2", sql2);
                return _baseRepository.ExecuteText<Marketing_TheWinning>(sql2);
            }
            return null;
        }


        #endregion

    }
}
